﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ZeroBug.PersonalityEvaluation
{
    public class SQLiteDatabaseHelper : IDatabase
    {
        private string _databasePath;
        public SQLiteDatabaseHelper()
        {
        }
        public DataTable GetTableStatus()
        {
            using (SQLiteConnection conn = new SQLiteConnection(string.Format("data source={0}", _databasePath)))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;

                    SQLiteHelper sh = new SQLiteHelper(cmd);

                    DataTable dt = sh.GetTableStatus();

                    conn.Close();

                    return dt;
                }
            }
        }
        public void CreateTable(string tableName, List<SQLiteColumn> columns)
        {
            SQLiteTable tb = new SQLiteTable(tableName);
            foreach (var col in columns)
            {
                tb.Columns.Add(col);
            }
            using (SQLiteConnection conn = new SQLiteConnection(string.Format("data source={0}", _databasePath)))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;

                    SQLiteHelper sh = new SQLiteHelper(cmd);

                    sh.DropTable(tableName);
                    sh.CreateTable(tb);

                    conn.Close();
                }
            }
        }
        public void InsertOneRecord(string tableName, Dictionary<string, object> data)
        {
            using (SQLiteConnection conn = new SQLiteConnection(string.Format("data source={0}", _databasePath)))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    cmd.Connection = conn;
                    conn.Open();

                    SQLiteHelper sh = new SQLiteHelper(cmd);

                    sh.Insert(tableName, data);

                    conn.Close();
                }
            }
        }
        public void UpdateOneRecord(string tableName, Dictionary<string, object> data, Dictionary<string, object> cond)
        {
            using (SQLiteConnection conn = new SQLiteConnection(string.Format("data source={0}", _databasePath)))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    cmd.Connection = conn;
                    conn.Open();

                    SQLiteHelper sh = new SQLiteHelper(cmd);

                    sh.Update(tableName, data, cond);

                    conn.Close();
                }
            }
        }
        public DataTable Select(string sql, out string errInfo)
        {
            errInfo = "";
            using (SQLiteConnection conn = new SQLiteConnection(string.Format("data source={0}", _databasePath)))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;

                    SQLiteHelper sh = new SQLiteHelper(cmd);
                    DataTable dt = null;
                    try
                    {
                        dt = sh.Select(sql);
                    }
                    catch (Exception ex)
                    {
                        errInfo = ex.Message;
                    }

                    conn.Close();
                    return dt;
                }
            }
        }

        public void SetDatabasePath(string path)
        {
            _databasePath = path;
        }
    }
}
